package com.sysmanage.etl.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.sysmanage.etl.model.ETL_Column;
import com.sysmanage.etl.model.ETL_Property_Column;
import com.sysmanage.etl.model.ETL_Rule;

/**
 * @author Yu Jou 2014年7月11日
 */
public class ExcelUtils {

	
	public static String checkExcel(File execelFile, Integer rowIndex, List<ETL_Rule> constraints ) {
		Workbook wb = null;
		Sheet sheet = null;
		Integer lastRowNum = null;
		Integer numberOfCells = null;
		List<ETL_Property_Column> pcs = new ArrayList<ETL_Property_Column>();
		try {
			for (ETL_Rule rule : constraints) {
				pcs.addAll(rule.getPropertyColumns());
			}
			wb = WorkbookFactory.create(execelFile);
			sheet = wb.getSheetAt(0);
			lastRowNum = sheet.getLastRowNum();
			numberOfCells = sheet.getRow(rowIndex).getPhysicalNumberOfCells();
		} catch (Exception e) {
			return "文件异常，请重新上传";
		}
		Row row = null;
		Cell cell = null;
		for (int i = rowIndex; i <= lastRowNum; i++) {
			row = sheet.getRow(i);
			for (int j = 0; j < numberOfCells; j++) {
				cell = row.getCell(j);
				String cellValue = getCellFormatValue(cell);
				for (ETL_Property_Column pc : pcs) {
					ETL_Column column = pc.getColumn();
					if(column == null){
						continue;
					}
					Integer cellIndex = column.getColumn_cellIndex();
					if(cellIndex == j) {
						String classType = pc.getProperty().getProperty_classType();
						try {
							if(pc.getIsRepeat() && StringUtils.isBlank(cellValue)){
								return column.getColumn_name() + "不能为空";
							} else if ("java.sql.Timestamp".equals(classType)) {
								try {
									Class<?> clzz = Class.forName(classType);
									Method method = clzz.getMethod("valueOf", String.class);
									method.invoke(clzz, cellValue);
								} catch (Exception ee) {
									return "第" + (i + 1) +"行 [" +column.getColumn_name() + "] 日期格式有误,请重新上传";
								}
							}
						} catch (Exception e) {
							return "第" + (i + 1) +"行 [" +column.getColumn_name() + "] 数据错格式有误,请重新上传";
						}
					}
				}
			}
		}
		return null;
	}
	/**
	 * 获取Execel文件 索引和title
	 * 
	 * @param execelFile
	 *            execel文件
	 * @param index
	 *            title所在行索引
	 * @return Map<行索引,Title>
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static Map<Integer, String> readExcelTitle(File execelFile, Integer rowIndex) throws InvalidFormatException, IOException {

		Workbook wb = WorkbookFactory.create(execelFile);
		Sheet sheet = wb.getSheetAt(0);
		Row row = sheet.getRow(rowIndex - 1);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		Map<Integer, String> tileMap = new HashMap<Integer, String>(colNum);

		for (int i = 0; i < colNum; i++) {
			String content = getCellFormatValue(row.getCell(i));
			if (content != null && !content.trim().isEmpty()) {
				tileMap.put(i, getCellFormatValue(row.getCell(i)));
			}
		}

		return tileMap;
	}

	/**
	 * 获取Execel 内容
	 * 
	 * @param execelFile
	 * @param index
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * 
	 *             TODO://待修改 数据量大时 可能造成内存益处 .
	 */
	public static List<Map<Integer, String>> readExcelContentList(File execelFile, Integer index) throws InvalidFormatException, IOException {

		List<Map<Integer, String>> data = new ArrayList<Map<Integer, String>>();
		Map<Integer, String> content = null;

		Workbook wb = WorkbookFactory.create(execelFile);
		Sheet sheet = wb.getSheetAt(0);
		Integer lastRowNum = sheet.getLastRowNum();
		Integer numberOfCells = sheet.getRow(index).getPhysicalNumberOfCells();

		Row row = null;
		Cell cell = null;
		for (int i = index; i <= lastRowNum; i++) {
			row = sheet.getRow(i);
			content = new HashMap<Integer, String>();
			for (int j = 0; j < numberOfCells; j++) {
				cell = row.getCell(j);
				String cellValue = getCellFormatValue(cell);
				if (cellValue != null && !cellValue.isEmpty()) {
					content.put(j, cellValue.trim());
				}
			}
			data.add(content);
		}
		return data;
	}

	public static List<Map<Integer, String>> readExcelContent(File execelFile, Integer... indexs) throws Exception {
		List<Map<Integer, String>> datas = new ArrayList<Map<Integer, String>>();
		Workbook wb = WorkbookFactory.create(execelFile);
		Sheet sheet = wb.getSheetAt(0);
		for (Integer index : indexs) {
			index --;
			Integer numberOfCells = sheet.getRow(index).getPhysicalNumberOfCells();
			Row row = sheet.getRow(index);
			Map<Integer, String> content = new HashMap<Integer, String>();
			for (int j = 0; j < numberOfCells; j++) {
				Cell cell = row.getCell(j);
				String cellValue = getCellFormatValue(cell);
				if (StringUtils.isNotBlank(cellValue)) {
					content.put(j, cellValue.trim());
				}
			}
			datas.add(content);
		}
		return datas;

	}

	private static String getCellFormatValue(Cell cell) {
		String cellValue = null;
		if (cell == null) {
			return cellValue;
		}
		int cellType = cell.getCellType();
		switch (cellType) {
		case Cell.CELL_TYPE_STRING: {
			cellValue = cell.getRichStringCellValue().getString().trim();
			break;
		}
		case Cell.CELL_TYPE_FORMULA: {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cellValue = cell.getRichStringCellValue().getString().trim();
			break;
		}
		case Cell.CELL_TYPE_NUMERIC: {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				cellValue = sdf.format(date);

			} else {// 如果是纯数字
				cell.setCellType(Cell.CELL_TYPE_STRING);
				String value = cell.getRichStringCellValue().getString().trim();
				;
				if (value.endsWith(".0")) {
					int index = value.lastIndexOf(".0");
					if (index > -1) {
						value = value.substring(0, index);
					}
				}
				cellValue = value;
			}
			break;
		}
		case Cell.CELL_TYPE_BOOLEAN: {
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		}
		default:
			break;
		}
		return cellValue;
	}

	/**
	 * 给指定行着色--另存为其他文件
	 * 
	 * @param sourcelFile
	 *            源文件
	 * @param destFile
	 *            目标文件
	 * @param rows
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static void colorExcel(File sourcelFile, Integer index, List<Integer> rows) {

		if (rows == null || rows.size() <= 0) {
			return;
		}
		FileOutputStream fileOut = null;
		try {
			String destFile = sourcelFile.getAbsolutePath() + "_temp";

			Workbook wb = WorkbookFactory.create(sourcelFile);
			CellStyle style = wb.createCellStyle();
			style.setFillForegroundColor(IndexedColors.RED.getIndex());
			style.setFillPattern(CellStyle.SOLID_FOREGROUND);
			Sheet sheet = wb.getSheetAt(0);
			Integer numberOfCells = sheet.getRow(index).getPhysicalNumberOfCells();
			System.out.println(numberOfCells);
			Row row = null;
			Cell cell = null;
			for (Integer rowIndex : rows) {
				row = sheet.getRow(index + rowIndex);
				for (int j = 0; j < numberOfCells; j++) {
					if (row != null) {
						cell = row.getCell(j);
						if (cell != null) {
							cell.setCellStyle(style);
						}
					}
				}
			}
			fileOut = new FileOutputStream(destFile);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取指定行内容
	 * 
	 * @param execelFile
	 * @param rows
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static List<Map<Integer, String>> readExcelRowsContentList(File execelFile, List<Integer> rows) throws InvalidFormatException, IOException {

		List<Map<Integer, String>> data = new ArrayList<Map<Integer, String>>();
		Map<Integer, String> content = null;

		Workbook wb = WorkbookFactory.create(execelFile);
		Sheet sheet = wb.getSheetAt(0);
		Integer numberOfCells = sheet.getRow(0).getPhysicalNumberOfCells();
		Row row = null;
		Cell cell = null;
		for (Integer rowIndex : rows) {
			row = sheet.getRow(rowIndex);
			content = new HashMap<Integer, String>();
			for (int j = 0; j < numberOfCells; j++) {
				cell = row.getCell(j);
				String cellValue = getCellFormatValue(cell);
				if (cellValue != null && !cellValue.isEmpty()) {
					content.put(j, cellValue.trim());
				}
			}
			data.add(content);
		}
		return data;
	}
}
